package cn.ptu.pmaa.test;

import cn.ptu.pmaa.entities.Student;
import cn.ptu.pmaa.mapper.AdminMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.ResourceUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class pomi {

    @Autowired
    AdminMapper adminMapper;

    @Test
    public void getExcel() throws IOException {



        // 定义一个数据格式化对象
        XSSFWorkbook wb = null;
        //获取sheet表格，及读取单元格内容
        XSSFSheet sheet = null;
        try {
            //excel模板路径
            String file = "E:\\ProgramData\\CodeDocument\\pmaa\\a.xlsx";

            File cfgFile = ResourceUtils.getFile(file);
            InputStream in = new FileInputStream(cfgFile);
            //读取excel模板
            wb = new XSSFWorkbook(in);

            sheet = wb.getSheetAt(0);

            List<Student> list = new ArrayList<>();
            Student student = new Student();
            //student.setRole("student");
            //System.out.println(sheet.getLastRowNum());
            for (int i=1;i<=sheet.getLastRowNum();i++){
                sheet.getRow(i).getCell(1).setCellType(CellType.STRING);
                sheet.getRow(i).getCell(0).setCellType(CellType.STRING);
                String cellValue0 = sheet.getRow(i).getCell(0).getStringCellValue();
                String cellValue1 = sheet.getRow(i).getCell(1).getStringCellValue();
                student.setStudentId(cellValue0);student.setStudentPwd(cellValue1);
                list.add(student);
                //System.out.println(cellValue0.trim()+"..."+cellValue1.trim());
            }
//            adminMapper.addAll(list);

            //先将获取的单元格设置为String类型，下面使用getStringCellValue获取单元格内容
            //如果不设置为String类型，如果单元格是数字，则报如下异常
            //java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
            //sheet.getRow(1).getCell(1).setCellType(CellType.STRING);
            //读取单元格内容
            //String cellValue = sheet.getRow(1).getCell(1).getStringCellValue();
            //System.out.println(cellValue.trim());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void addExcel() throws IOException {
        //新建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建sheet页
        //创建sheet页
        workbook.createSheet("测试页");
        workbook.createSheet("第二页");
        FileOutputStream fout = new FileOutputStream("a.xlsx");

        workbook.write(fout);
        fout.close();
        workbook.close();
    }



    @Test
    public void insertAll(){


    };


}
